-- 创建主表
CREATE TABLE prompt_template (
    id int4 PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    template_content TEXT NOT NULL,
    category VARCHAR(50) NOT NULL,
    style VARCHAR(50),
    variables JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- 创建版本表
CREATE TABLE prompt_template_version (
    id int4 PRIMARY KEY,
    template_id int4 NOT NULL REFERENCES prompt_template(id) ON DELETE CASCADE,
    version VARCHAR(20) NOT NULL,
    template_content TEXT NOT NULL,
    variables JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(template_id, version)
);

-- 创建标签表
CREATE TABLE prompt_template_tag (
    id int4 PRIMARY KEY,
    template_id int4 NOT NULL REFERENCES prompt_template(id) ON DELETE CASCADE,
    tag VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(template_id, tag)
);

-- 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为所有表添加更新时间触发器
CREATE TRIGGER trigger_update_prompt_template_updated_at
    BEFORE UPDATE ON prompt_template
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER trigger_update_template_version_updated_at
    BEFORE UPDATE ON prompt_template_version
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();